package com.myeasyexcel.utils;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.util.CollectionUtils;

import java.util.Map;

/**
 * @author wangXiaoHu
 * @date 2022/06/15 11:45
 **/
public class InfiniteDropDownHandler implements SheetWriteHandler {
    private Map<Integer, String[]> dropDownMap;

    public InfiniteDropDownHandler(Map<Integer, String[]> dropDownMap) {
        this.dropDownMap = dropDownMap;
    }

    /**
     * 创建sheet页前的操作
     *
     * @param writeWorkbookHolder
     * @param writeSheetHolder
     */
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    }

    /**
     * 创建sheet页后的操作
     *
     * @param writeWorkbookHolder
     * @param writeSheetHolder
     */
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        if(CollectionUtils.isEmpty(dropDownMap)){
            return;
        }
        DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();

        // 创建sheet，突破下拉框255的限制
        //获取一个workbook
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        //定义sheet的名称
        String sheetName = "dropdown";
        //1.创建一个隐藏的sheet 名称为 proviceSheet
        Sheet proviceSheet = workbook.createSheet(sheetName);


        for(Map.Entry<Integer,String[]> entry:dropDownMap.entrySet()){
            // 设置隐藏
            workbook.setSheetHidden(workbook.getSheetIndex(proviceSheet),true);
            String[] strings = entry.getValue();
            //2.循环赋值（为了防止下拉框的行数与隐藏域的行数相对应，将隐藏域加到结束行之后）
            for (int i = 0, length = strings.length; i < length; i++) {
                // i:表示你开始的行数  0表示你开始的列数
                proviceSheet.createRow(i).createCell(0).setCellValue(strings[i]);
            }
            Name category1Name = workbook.createName();
            category1Name.setNameName(sheetName);
            //4 $A$1:$A$N代表 以A列1行开始获取N行下拉数据
            category1Name.setRefersToFormula(sheetName + "!$A$1:$A$" + (strings.length));
            //5 将刚才设置的sheet引用到你的下拉列表中
            Integer col = entry.getKey();
            CellRangeAddressList addressList = new CellRangeAddressList(2, PoiUtil.LAST_ROW, col, col);
            DataValidationConstraint constraint8 = helper.createFormulaListConstraint(sheetName);
            DataValidation dataValidation3 = helper.createValidation(constraint8, addressList);
            writeSheetHolder.getSheet().addValidationData(dataValidation3);
        }
    }
}
